package br.com.rafa3d.dao;


import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLDataException;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.util.ArrayList;
import java.util.List;

import javax.faces.application.FacesMessage;
import javax.faces.context.FacesContext;

import br.com.rafa3d.conexao.ConexaoPool;
import br.com.rafa3d.conexao.ConexaoSQLServer;
import br.com.rafa3d.modelo.Cliente;
import br.com.rafa3d.modelo.ContaCliente;


public class ClienteDao implements ICantinaDao, Serializable {
	
	private static final long serialVersionUID = -561477182630099595L;
	//Connection con = ConexaoSQLServer.getConnection();
	Connection con = ConexaoPool.getConnexao();
	
		
	@Override
	public void gravar(Object object) {
		String sql = "INSERT INTO CLIENTE" + " VALUES (?,?,?,?,?,?,?,?,?,?,?)";
		Cliente cliente = (Cliente) object;
		PreparedStatement pstm = null;
		try {
			pstm = con.prepareStatement(sql);
			pstm.setString(1, cliente.getNome());
			pstm.setString(2, cliente.getTelefone());
			pstm.setString(3, cliente.getCelular());
			pstm.setString(4, cliente.getRua());
			pstm.setString(5, cliente.getBairro());
			pstm.setString(6, cliente.getCidade());		
			pstm.setString(7, cliente.getEstado());	
			pstm.setString(8, cliente.getComp());	
			pstm.setString(9, cliente.getEmail());
			pstm.setString(10, cliente.getFuncao());
			pstm.setInt(11, cliente.getCodCarteira());
			//pstm.execute();
			pstm.executeUpdate();
		}catch (SQLDataException e) {
			e.printStackTrace();
		}catch (SQLIntegrityConstraintViolationException ex) {
			ex.printStackTrace();
		} catch (SQLException e){
			e.printStackTrace();
		}finally{
			try {
				pstm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	@Override
	public void atualizar(Object object) {
		
		
	}
	
	@Override
	public void deletar(int cod) {
		FacesContext context = FacesContext.getCurrentInstance();
		String sql = "DELETE CLIENTE WHERE COD = ?";
		PreparedStatement pstm = null;
		try {
			pstm = con.prepareStatement(sql);
			pstm.setInt(1, cod);
			pstm.execute();
			pstm.close();
		} catch (SQLException e) {
			e.printStackTrace();
			context.addMessage(null, new FacesMessage(FacesMessage.SEVERITY_ERROR, "Erro", "houve um erro ao deletar cliente"));
		}/*finally{
			try {
				pstm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}*/
	}

	@Override
	public Object buscarCod(int cod) {
		String sql = "SELECT * FROM CLIENTE WHERE COD = ?";
		Cliente cliente = new Cliente();
		PreparedStatement pstm = null;
		try {
			pstm = con.prepareStatement(sql);
			pstm.setInt(1, cod);
			ResultSet rs = pstm.executeQuery();
			if (rs.next()) {
				cliente.setCod(rs.getInt(1));
				cliente.setNome(rs.getString(2));
				cliente.setTelefone(rs.getString(3));
				cliente.setCelular(rs.getString(4));
				cliente.setRua(rs.getString(5));
				cliente.setBairro(rs.getString(6));
				cliente.setCidade(rs.getString(7));
				cliente.setEstado(rs.getString(8));
				cliente.setComp(rs.getString(9));
				cliente.setEmail(rs.getString(10));
				cliente.setFuncao(rs.getString(11));
				cliente.setCodCarteira(rs.getInt(12));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				pstm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return cliente;
	}

	@Override
	public List<?> buscarNome(String nome) {
		String sql = "SELECT * FROM CLIENTE WHERE NOME LIKE '%"+ nome +"%'";
		List<Cliente> listCli = new ArrayList<Cliente>();
		Cliente cliente;
		PreparedStatement pstm = null;
		try {
			pstm = con.prepareStatement(sql);
			ResultSet rs = pstm.executeQuery();

			while (rs.next()) {
				cliente = new Cliente();
				cliente.setCod(rs.getInt(1));
				cliente.setNome(rs.getString(2));
				cliente.setTelefone(rs.getString(3));
				cliente.setCelular(rs.getString(4));
				cliente.setRua(rs.getString(5));
				cliente.setBairro(rs.getString(6));
				cliente.setCidade(rs.getString(7));
				cliente.setEstado(rs.getString(8));
				cliente.setComp(rs.getString(9));
				cliente.setEmail(rs.getString(10));
				cliente.setFuncao(rs.getString(11));
				cliente.setCodCarteira(rs.getInt(12));
				listCli.add(cliente);
			}/*else{
				throw new SQLException("Nenhum Produto encontrado por nome");
			}*/
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				pstm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return listCli;
	}

	@Override
	public List<?> listarTodos() {
		String sql = "SELECT * FROM CLIENTE ORDER BY NOME ASC";
		List<Cliente> listaCliente = new ArrayList<Cliente>();
		PreparedStatement pstm = null;
		
		try {
			pstm = con.prepareStatement(sql);
			ResultSet rs = pstm.executeQuery();
			while (rs.next()) {
				Cliente cliente = new Cliente();
				
				cliente.setCod(rs.getInt(1));
				cliente.setNome(rs.getString(2));
				cliente.setTelefone(rs.getString(3));
				cliente.setCelular(rs.getString(4));
				cliente.setRua(rs.getString(5));
				cliente.setBairro(rs.getString(6));
				cliente.setCidade(rs.getString(7));
				cliente.setEstado(rs.getString(8));
				cliente.setComp(rs.getString(9));
				cliente.setEmail(rs.getString(10));
				cliente.setFuncao(rs.getString(11));
				cliente.setCodCarteira(rs.getInt(12));
				
				listaCliente.add(cliente);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}/*finally{
			try {
				pstm.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}*/
		return listaCliente;
	}

	public List<?> listarContaClientes() {
		String sql = "{call SPC_CONTA_CLIENTES}";
		List<ContaCliente> listaConta = new ArrayList<ContaCliente>();
		ContaCliente contaCli;
		CallableStatement call = null;
		try {
			call = con.prepareCall(sql);
			ResultSet rs = call.executeQuery();
			while (rs.next()) {
				contaCli = new ContaCliente(rs.getInt(1), rs.getString(2), rs.getDouble(3));
				listaConta.add(contaCli);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				call.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return listaConta;
	}

}
